﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Collections;

namespace CubeDAL
{
    public class MySQLWrapper
    {

        private string server, database, user, pwd;
        private string err_msg = "";
        private MySqlConnection conn;
        public MySQLWrapper(string server, string database, string user, string pwd) {
            this.server = server;
            this.database = database;
            this.user = user;
            this.pwd = pwd;
            Initial();
        }

        public string ErrorMessage 
        {
            get { return err_msg; }
            set { err_msg = value; }
        }

        public List<List<string>> Select(string sql, string[] output_fields) {
            List<List<string>> result = new List<List<string>>();

            if (this.OpenConnection() == true) { 
                // Create command
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                // Create data reader & execute command
                MySqlDataReader reader = cmd.ExecuteReader();

                List<string> row;

                while (reader.Read()) {
                    row = new List<string>();
                    for (int i = 0; i < output_fields.Length; ++i) {
                        row.Add(reader[output_fields[i]] + "");
                    }
                    result.Add(row);
                }

                reader.Close();
            }

            this.CloseConnection();

            return result;
        }

        public int Insert(string sql)
        {
            if (this.OpenConnection() == true)
            {
                MySqlCommand usingUtf = new MySqlCommand(@"set names utf8;", conn);
                usingUtf.ExecuteNonQuery();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                try
                {
                    int status = cmd.ExecuteNonQuery();
                    this.CloseConnection();
                    return status;
                }
                catch (MySqlException e)
                {
                    this.CloseConnection();
                    this.err_msg = e.Message;
                    return -1;
                }

            }
            this.CloseConnection();
            return -1;
        }


        private bool CloseConnection() {
            try {
                conn.Close();
                return true;
            } catch (MySqlException e) {
                throw new Exception(e.Message);
            }
        }   

        private bool OpenConnection() {
            try
            {
                conn.Open();
                return true;
            }
            catch (MySqlException e) {
                //When handling errors, you can your application's response based on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (e.Number) { 
                    case 0:
                        throw new Exception("Cannot connect to server.");
                    case 1045:
                        throw new Exception("Invalid user name and/or password.");
                }
                return false;
            }
        }

        private void Initial() {
            string connString;
            connString = "SERVER=" + server + ";"
                        +"DATABASE=" + database + ";"
                        +"UID=" + user + ";"
                        +"PASSWORD=" + pwd + ";"+"Charset=utf8;";
            conn = new MySqlConnection(connString);
        }
    }
}
